package com.tosmart.jorm.util;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Radiantek ChongQing China .Ltd
 * User: Administrator
 * Date: 2009-7-21
 * Time: 13:51:53
 */
public class DBBase {

    /**
     * Constructor
     *
     * @param connection Database Connection
     * @param autoClose  Done, whether to automatically
     *                   close the data connection.
     */
    public DBBase(Connection connection, boolean autoClose) {
        this.connection = connection;
        this.autoClose = autoClose;
    }

    /**
     * Implementation of the select statement with a variable
     * parameter and to return query results in tabular form
     *
     * @param sql  SQL Statement
     * @param args SQL query a list of parameters that need to
     * @return Query results, each List item, said his party
     *         records, the first record kept by a Map
     */
    public List<Map<String, Object>> select(
            String sql, Object... args) {

        List<Map<String, Object>> result =
                new ArrayList<Map<String, Object>>();

        ResultSet resultSet = null;
        PreparedStatement stmt = null;

        try {
            stmt = connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++)
                bindParams(stmt, i + 1, args[i]);

            resultSet = stmt.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();

            List<String> names = new ArrayList<String>();
            for (int i = 1; i <= count; i++)
                names.add(metaData.getColumnName(i).toUpperCase());

            while (resultSet.next()) {

                Map<String, Object> record = new HashMap<String, Object>();

                for (String name : names)
                    record.put(name, resultSet.getObject(name));

                result.add(record);
            }
        }
        catch (SQLException e) {
            System.err.println("select:" + e.getMessage());
        }
        finally {
            release(connection, stmt, resultSet);
        }

        return result;
    }

    /**
     * Execute a update or insret SQL statement
     *
     * @param sql  SQL statement
     * @param args SQL query a list of parameters that need to
     * @return -1: success
     *         >=0: The number of records affected
     */
    public int execute(String sql, Object... args) {

        PreparedStatement stmt = null;

        try {
            stmt = connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++)
                bindParams(stmt, i + 1, args[i]);

            return stmt.executeUpdate();
        }
        catch (SQLException e) {
            System.err.println("execute:" + e.getMessage());
        }
        finally {
            release(connection, stmt, null);
        }

        return -1;
    }

    /**
     * To determine the existence of a table.
     * @param tableName the name of the table
     * @return exists or not
     */
    public boolean existsTable(String tableName) {

        String sql = String.format(
                "select * from %s where 1=0",
                tableName
        );

        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            stmt.executeQuery(sql);
            return true;
        }
        catch (SQLException e) {
            return false;
        }
        finally {
            release(connection, stmt, null);
        }
    }

    /**
     * Choose only one result for the field, and the
     * result set only a single line of SQL queries,
     * this method made a one-time query results returned.
     *
     * @param sql  SQL statement
     * @param args SQL query a list of parameters that need to
     * @return one-time query results, return null if not found
     */
    public Object selectObject(String sql, Object... args) {

        ResultSet resultSet = null;
        PreparedStatement stmt = null;

        try {
            stmt = connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++)
                bindParams(stmt, i + 1, args[i]);

            resultSet = stmt.executeQuery();

            if (resultSet.next())
                return resultSet.getObject(1);
        }
        catch (SQLException e) {
            System.err.println("selectObject:" + e.getMessage());
        }
        finally {
            release(connection, stmt, resultSet);
        }

        return null;
    }

    /**
     * This method is the method the results of a
     * selectObject packaging of its results
     * directly into Integer.
     *
     * @param sql  SQL statement
     * @param args SQL query a list of parameters that need to
     * @return one-time query results, return -1 if not found
     */
    public int selectInt(String sql, Object... args) {
        return castInt(selectObject(sql, args));
    }

    /**
     * This method is the method the results of a
     * selectObject packaging of its results
     * directly into Long.
     *
     * @param sql  SQL statement
     * @param args SQL query a list of parameters that need to
     * @return one-time query results, return -1 if not found
     */
    public long selectLong(String sql, Object... args) {
        return castLong(selectObject(sql, args));
    }

    /**
     * This method is the method the results of a
     * selectObject packaging of its results
     * directly into String.
     *
     * @param sql  SQL statement
     * @param args SQL query a list of parameters that need to
     * @return one-time query results, return null if not found
     */
    public String selectString(String sql, Object... args) {
        return castString(selectObject(sql, args));
    }

    /**
     * This method is the method the results of a
     * selectObject packaging of its results
     * directly into Date.
     *
     * @param sql  SQL statement
     * @param args SQL query a list of parameters that need to
     * @return one-time query results, return null if not found
     */
    public Date selectDate(String sql, Object... args) {
        return castDate(selectObject(sql, args));
    }

    /**
     * The one read from the database into an Integer value.
     *
     * @param obj value read from the database
     * @return the int result of convert operate
     */
    public int castInt(Object obj) {

        if (obj == null) return 0;

        if (obj instanceof Integer)
            return (Integer) obj;

        if (obj instanceof BigDecimal)
            return ((BigDecimal) obj).intValue();

        if (obj instanceof BigInteger)
            return ((BigInteger) obj).intValue();

        if (obj instanceof String) {
            String strValue = castString(obj).trim();
            if (strValue.equals("")) return 0;
            return Integer.parseInt(strValue);
        }

        String clzName = obj.getClass().getName();
        String message = "Cast " + clzName + " to Integer.";

        throw new RuntimeException(message);
    }

    /**
     * The one read from the database into an Long value.
     *
     * @param obj value read from the database
     * @return the int result of convert operate
     */
    public long castLong(Object obj) {

        if (obj == null) return 0;

        if (obj instanceof Long)
            return (Long) obj;

        if (obj instanceof BigDecimal)
            return ((BigDecimal) obj).longValue();

        if (obj instanceof BigInteger)
            return ((BigInteger) obj).longValue();

        if (obj instanceof String) {
            String strValue = castString(obj).trim();
            if (strValue.equals("")) return 0;
            return Long.parseLong(strValue);
        }

        String clzName = obj.getClass().getName();
        String message = "Cast " + clzName + " to Long.";

        throw new RuntimeException(message);
    }

    /**
     * The one read from the database into an String value.
     *
     * @param obj value read from the database
     * @return the int result of convert operate
     */
    public String castString(Object obj) {

        if (obj == null) return "";

        if (obj instanceof String)
            return (String) obj;

        return obj.toString();
    }

    /**
     * The one read from the database into an Date value.
     *
     * @param obj value read from the database
     * @return the int result of convert operate
     */
    public Date castDate(Object obj) {

        if (obj == null) return null;

        if (obj instanceof Date)
            return (Date) obj;

        String clzName = obj.getClass().getName();
        String message = "Cast " + clzName + " to Date.";

        throw new RuntimeException(message);
    }

    /**
     * Set the parameter list for the java.sql.Statement class.
     *
     * @param stmt  PreparedStatement instance
     * @param index the index of the parameter
     * @param value the value
     * @throws SQLException Possible throw by PreparedStatement set...  
     */
    protected void bindParams(
            PreparedStatement stmt, int index, Object value)
            throws SQLException {

        if (value == null) {
            stmt.setObject(index, null);
        }
        else if (String.class.equals(value.getClass())) {
            stmt.setString(index, (String) value);
        }
        else if (Integer.class.equals(value.getClass())) {
            stmt.setInt(index, (Integer) value);
        }
        else if (Long.class.equals(value.getClass())) {
            stmt.setLong(index, (Long) value);
        }
        else if (Double.class.equals(value.getClass())) {
            stmt.setDouble(index, (Double) value);
        }
        else if (Float.class.equals(value.getClass())) {
            stmt.setFloat(index, (Float) value);
        }
        else if (value instanceof Calendar) {
            long inMillis = ((Calendar) value).getTimeInMillis();
            stmt.setTimestamp(index, new Timestamp(inMillis));
        }
        else if (value instanceof Date) {
            long time = ((Date) value).getTime();
            stmt.setTimestamp(index, new Timestamp(time));
        }
        else if (Boolean.class.equals(value.getClass())) {
            stmt.setBoolean(index, (Boolean) value);
        }
        else if (Byte.class.equals(value.getClass())) {
            stmt.setByte(index, (Byte) value);
        }
        else if (Short.class.equals(value.getClass())) {
            stmt.setShort(index, (Short) value);
        }
        else if (Character.class.equals(value.getClass())) {
            stmt.setString(index, value.toString());
        }
        else if (BigDecimal.class.equals(value.getClass())) {
            stmt.setBigDecimal(index, (BigDecimal) value);
        }
        else {
            stmt.setObject(index, value);
        }
    }

    /**
     * release the resultset/statement/connection
     *
     * @param connection Database Connection
     * @param stmt       Statement object, ignore if null
     * @param resultSet  The ResultSet instance, ignore if null
     */
    protected void release(
            Connection connection,
            Statement stmt, ResultSet resultSet) {

        try {
            if (resultSet != null) resultSet.close();
            if (stmt != null) stmt.close();
            if (autoClose) connection.close();
        }
        catch (SQLException e) {
            System.err.println("release:" + e.getMessage());
        }
    }

    protected Connection connection;
    protected boolean autoClose;
}
